package cn.xiezonjie.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import cn.common.dao.BaseDao;
import cn.common.tool.seach;
import cn.xiezonjie.model.Menu;

public class MenuDao extends BaseDao{
	
	Connection conn = null;
	 Statement stmt = null;
	 PreparedStatement pstmt=null;
	 ResultSet rs=null;
	/*计算菜品总条数,并且处理页面传进来的当前页数，进行过滤操作,并返回一个合法当前页数*/	
	public int CountPage(int page_now_c) throws SQLException
	{
		
		seach sc = new seach();
		int page_now = page_now_c;
		ResultSet rs ;
		String sql="select count (*) from menu";
		
		rs=sc.f(sql);
		rs.next();
		
		/*计算总条数，判断一下有几页*/   
		int pagesum=rs.getInt(1)/5;
		
		if(pagesum<1)
		{
			pagesum=1;/*当前的数据库的总条数不足5条,直接显示第一页*/
		}
		
		if(page_now>pagesum)/*当前页数大于总页数*/
		{
			page_now=pagesum;
		}
		else if(page_now<1) /*当前页数小于1*/
		{
			page_now=1;
		}
		
		return page_now;
	}
	/*计算菜品总条数,并且处理页面传进来的当前页数，进行过滤操作,并返回一个合法当前页数*/
	
	
	
	
	/*计算菜品总条数,并且处理页面传进来的当前页数，进行过滤操作,并返回一个合法当前页数*/	
	public int CountPage_search(int page_now_c ,String type,String keyword) throws SQLException
	{
		
		seach sc = new seach();
		int page_now = page_now_c;
		ResultSet rs ;
		String sql="select count (*) from menu where m_name  like '%" +keyword+"%'";
		
		rs=sc.f(sql);
		rs.next();
		
		/*计算总条数，判断一下有几页*/   
		int pagesum=rs.getInt(1)/5;
		
		if(pagesum<1)
		{
			pagesum=1;/*当前的数据库的总条数不足5条,直接显示第一页*/
		}
		
		if(page_now>pagesum)/*当前页数大于总页数*/
		{
			page_now=pagesum;
		}
		else if(page_now<1) /*当前页数小于1*/
		{
			page_now=1;
		}
		
		return page_now;
	}
	/*计算菜品总条数,并且处理页面传进来的当前页数，进行过滤操作,并返回一个合法当前页数*/
	
	
	
	
	
	
	/*另外加的一个用存储过程做的分页方法*/
	public ArrayList<Menu> query_storage
	( int page_now , int page_size , String type , String keyword , String srot ) throws SQLException
	{
		String sql = null;
		seach sc = new seach();
		ResultSet rs ;
		ArrayList<Menu> list=new ArrayList<Menu>();
		Menu menu1;
		
		
		if(keyword==null)//查询全部  按o_number降序排列
		{
					sql= "declare @t_name nvarchar(20) ;"+ 
						"declare @t_where nvarchar(20) ;"+
						"declare @t_sort nvarchar(20) ;"+ 
						"declare @page_now int ;"+ 
						"set @t_name = '*';"+ //查询所有字段52
						"set @t_where ='t_id=1';"+
						"set @t_sort = 'm_id ';"+
						"set @page_now = "+page_now+" "+
						"exec Paging_commom menu, @t_name,0,@t_where,5,@page_now,@t_sort";
		}
		else if(keyword!=null)
		{
			sql= 	"declare @t_name nvarchar(20) ;"+ 
					"declare @t_where nvarchar(20) ;"+
					"declare @t_sort nvarchar(20) ;"+ 
					"declare @page_now int ;"+ 
					"set @t_name = '*';"+ //查询所有字段
								 //'m_name like+'+'''%'+'烧'+'%''
					"set @t_where ='m_name like+'+'''%'+'"+keyword+"'+'%''';"+
					"set @t_sort = 'm_id';"+
					"set @page_now = "+page_now+" "+
					"exec Paging_commom menu, @t_name,1,@t_where,5,@page_now,@t_sort";
		}
		
		System.out.println("这是执行分页存储过程时的sql : "+sql);
		
		rs = sc.f(sql);
		while(rs!=null&&rs.next())
		{
			menu1 = new Menu(rs.getInt("m_id"), rs
   					.getString("m_name"), rs.getString("m_type"), rs
   					.getInt("m_price"), rs.getString("m_time"), rs
   					.getString("m_status"), rs.getString("m_describe"),
   					rs.getString("m_url_defal"),rs.getString("m_url_2"));
			menu1.setM_pagenow(page_now);
			list.add(menu1);
		}
		
		return list;
		
	}
	/*另外加的一个用存储过程做的方法*/
	
	
	
	public ArrayList<Menu> query(String Type, String Key) throws ParseException {
		int if_error=0;
		ArrayList<Menu> list = new ArrayList<Menu>();
		ResultSet rs = null;
		Menu menu1=null;
		String sql = "select * from menu where " + Type + " like ?";
		conn = this.getConn();
		try 
		{
			if (Type != null && !Type.trim().equals("")) 
			{
				pstmt = conn.prepareStatement(sql,
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				pstmt.setString(1, "%" + Key + "%");
				rs = pstmt.executeQuery();
			} 
			else 
			{
				sql = "select * from menu where m_status='1'";
				stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(sql);
			}
			
			
			while (rs != null && rs.next()) 
			{
				if (rs.getString("m_name") != null) 
				{
					if_error=1;
					menu1 = new Menu(rs.getInt("m_id"), rs
		   					.getString("m_name"), rs.getString("m_type"), rs
		   					.getInt("m_price"), rs.getString("m_time"), rs
		   					.getString("m_status"), rs.getString("m_describe"),
		   					rs.getString("m_url_defal"),rs.getString("m_url_2"));
					
					SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
					Date currentTime =formatter.parse(menu1.getM_time());
					menu1.setM_time(formatter.format(currentTime));
					list.add(menu1);
					
				}
			}
			
			if(if_error==0) return null;
			
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			closeAll();
		}
		return list;
		
	}
	

    public ArrayList<Menu> Search() throws ParseException{
     
   	 String sql="select * from menu where m_status='1'";
   	 ResultSet rs=null;
   	 Statement stmt =null;
   	 ArrayList<Menu> list=new ArrayList<Menu>();
   	 Connection conn=this.getConn();
   	 Menu menu2=null;
   	 try{
   		  stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
   		 rs=stmt.executeQuery(sql);
   		while (rs != null && rs.next()) {
			menu2 = new Menu(rs.getInt("m_id"), rs
   					.getString("m_name"), rs.getString("m_type"), rs
   					.getInt("m_price"), rs.getString("m_time"), rs
   					.getString("m_status"), rs.getString("m_describe"),
   					rs.getString("m_url_defal"),rs.getString("m_url_2"));
			
			SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
			Date currentTime =formatter.parse(menu2.getM_time());
			menu2.setM_time(formatter.format(currentTime));
			list.add(menu2);
		}
   	
       	}
   	 catch(SQLException e){e.printStackTrace();
   	 }
   	 return list;
    }

    
    public String Add(Menu menu){
    	System.out.println(menu.getM_id()+menu.getM_name());
    	conn = this.getConn();
    	String sql = "insert into menu(m_name,m_type,m_price,m_time,m_status,m_describe,m_url_defal) values (?,?,?,?,?,?,?)";
    	try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,menu.getM_name());
			pstmt.setString(2,menu.getM_type());
			pstmt.setInt(3,menu.getM_price() );
			pstmt.setString(4,menu.getM_time());
			pstmt.setString(5,menu.getM_status() );
			pstmt.setString(6,menu.getM_describe());
			pstmt.setString(7,menu.getM_url_defal());
			pstmt.executeUpdate();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
    
    public String delete(int m_id){
		String sql = "update menu set M_status='0' where m_id=?";
		conn = this.getConn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_id);
			pstmt.executeUpdate();
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
    
    
    public Menu SearchById(int m_id){
        
      	 String sql="select * from menu where m_id=?";
      	 ResultSet rs=null;
      	 
      	
      	 conn = this.getConn();
      	 Menu menu3=null;
      	 try{
      		pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, m_id);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
   			       menu3 = new Menu(rs.getInt("m_id"), rs
   					.getString("m_name"), rs.getString("m_type"), rs
   					.getInt("m_price"), rs.getString("m_time"), rs
   					.getString("m_status"), rs.getString("m_describe"),
   					rs.getString("m_url_defal"),rs.getString("m_url_2"));
   			       
   			      			
   		}
      	
          	}
      	 catch(SQLException e){e.printStackTrace();
      	 }
      	 return menu3;
       }
public String Update(Menu menu){
    	
    	conn = this.getConn();
    	
    	String sql = "update menu set M_name=?,M_type=?,M_price=?,M_time=?,M_status=?,M_describe=?,m_url_defal=? where M_id=?";
    	try {
			pstmt = conn.prepareStatement(sql);
			
			
			pstmt.setString(1,menu.getM_name());
			pstmt.setString(2,menu.getM_type());
			pstmt.setInt(3,menu.getM_price() );
			pstmt.setString(4,menu.getM_time());
			pstmt.setString(5,menu.getM_status() );
			pstmt.setString(6,menu.getM_describe());
			pstmt.setString(7,menu.getM_url_defal());
			pstmt.setInt(8,menu.getM_id());
			pstmt.executeUpdate();
			
			if (pstmt != null)
				pstmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
		return null;
	}
    
}
	
	


